﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Windows.Forms;
using VitCode.Web.UI.ExcelFileGenerator;
namespace DataOutput
{
    public partial class DataOutput : Form
    {
        private const string m_ConnStrTxtPath = "ConnStr.txt";
        private readonly StringBuilder EXPORTSQL_BySpeciality = new StringBuilder();

        public DataOutput()
        {
            InitializeComponent();
        }

       

        private string GetConnectionString()
        {
            string returnValue = @"Server=.\SqlExpress;uid=sa;pwd=vitcodesa;database=AMS";
            if (File.Exists(m_ConnStrTxtPath))
            {
                var sr = new StreamReader(m_ConnStrTxtPath);
                try
                {
                    returnValue = sr.ReadLine();
                }
                finally
                {
                    sr.Close();
                }
            }

            return returnValue;
        }

        private void DataOutput_Load(object sender, EventArgs e)
        {
            Txt_Server.Text = GetConnectionString();
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
        }

        private void Btn_SelectSavePath_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                Txt_Savepath.Text = folderBrowserDialog1.SelectedPath + "\\数据导出结果.xls";
            }
        }

        private void SaveConnectionString(string connStr)
        {
            if (File.Exists(m_ConnStrTxtPath))
                File.Delete(m_ConnStrTxtPath);
            var sw = new StreamWriter(m_ConnStrTxtPath);
            try
            {
                sw.WriteLine(connStr);
            }
            finally
            {
                sw.Close();
            }
        }

        private void OutPutExcel(DataTable resultData, string groupValue)
        {
            var excel = new ExcelFileGenerator();
            excel.MaxRowCount = Convert.ToInt32(Txt_MaxCount.Text);
            var tempFile = new FileInfo(Txt_Savepath.Text);
            string resultDir = tempFile.DirectoryName;
            string resultFileName = tempFile.Name;
            if (groupValue.Length > 0)
            {
                resultDir += "\\" + groupValue;
                if (Directory.Exists(resultDir))
                    Directory.Delete(resultDir, true);
                Directory.CreateDirectory(resultDir);
            }
            if (Radio_SaveSameFile.Checked)
            {
                string file = excel.ExportDataToExcelFile(resultData);
                File.Copy(file,
                          resultDir + "\\" + resultFileName.Substring(0, resultFileName.LastIndexOf('.')) + "_" +
                          groupValue + "_" + ".xls", true);
            }
            else
            {
                string[] files = excel.ExportDataToExcelFiles(resultData, Convert.ToInt32(Txt_FileRecordCount.Text));
                for (int i = 0; i < files.Length; i++)
                {
                    File.Copy(files[i],
                              resultDir + "\\" + resultFileName.Substring(0, resultFileName.LastIndexOf('.')) + "_" +
                              groupValue + "_" + i.ToString().PadLeft(3,'0') + ".xls", true);
                }
            }
        }

        private void OutPutExcelByGroup(DataTable resultData)
        {
            string strGroupName = txtGroupByColName.Text.Trim();
            DataTable tempDt = resultData.Clone();
            if (resultData.Columns.Contains(strGroupName))
            {
                string groupValue = "";
                for (int i = 0; i < resultData.Rows.Count; i++)
                {
                    DataRow dr = resultData.Rows[i];
                    string rGroupValue = Convert.ToString(dr[strGroupName]).Trim();
                    if (i == 0)
                    {
                        groupValue = rGroupValue;
                        tempDt.Rows.Add(dr.ItemArray);
                    }
                    else
                    {
                        if (groupValue != rGroupValue)
                        {
                            if (chIsDeleteGroupCol.Checked)
                                tempDt.Columns.RemoveAt(tempDt.Columns.IndexOf(strGroupName));

                            OutPutExcel(tempDt, groupValue);

                            tempDt.Rows.Clear();

                            if (chIsDeleteGroupCol.Checked)
                                tempDt = resultData.Clone();

                            groupValue = rGroupValue;
                        }
                        tempDt.Rows.Add(dr.ItemArray);
                    }
                }
                if (tempDt.Rows.Count > 0)
                {
                    if (chIsDeleteGroupCol.Checked)
                        tempDt.Columns.RemoveAt(tempDt.Columns.IndexOf(strGroupName));
                    OutPutExcel(tempDt, groupValue);
                    tempDt.Rows.Clear();
                    tempDt.Dispose();
                }
            }
            else
            {
                MessageBox.Show("分组列名在查询中不列在");
                return;
            }
        }

        private void OutPutExcel(DataTable resultData)
        {
            OutPutExcel(resultData, "");
        }

        private void Btn_Export_Click(object sender, EventArgs e)
        {
            SaveConnectionString(Txt_Server.Text);
            if (Txt_Savepath.Text.Trim().Length == 0)
            {
                MessageBox.Show("需要设置数据文件保存路径", "数据导出工具", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Txt_Savepath.Focus();
                return;
            }
            var fi = new FileInfo(Txt_Savepath.Text);
            if (fi.Extension.ToLower() != ".xls")
            {
                Txt_Savepath.Text += ".xls";
            }

            var oConn = new SqlConnection(Txt_Server.Text.Trim());
            try
            {
                oConn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("不能连接数据库，" + ex.Message, "数据导出工具", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            var cmd = new SqlCommand();
            cmd.CommandTimeout = 120;
            cmd.Connection = oConn;
            string strSql = Txt_SQL.Text.Trim().ToLower();
            if (txtGroupByColName.Text.Length > 0)
            {
                string orderStr = "";
                int lastOrderStrIndex = strSql.LastIndexOf("order by");
                if (lastOrderStrIndex > -1)
                {
                    orderStr = strSql.Substring(lastOrderStrIndex + 9).ToLower();
                    if (orderStr.IndexOf(txtGroupByColName.Text.ToLower()) < 0 ||
                        orderStr.IndexOf(',') < orderStr.IndexOf(txtGroupByColName.Text.ToLower()))
                    {
                        MessageBox.Show("请把分组列名写在Order by 语句的第一个条件上");
                        return;
                    }
                }
            }
            if (tabControl1.SelectedIndex == 0)
                cmd.CommandText = strSql;
            if (cmd.CommandText.Length == 0)
                return;
            var ad = new SqlDataAdapter(cmd);
            var resultData = new DataTable();
            try
            {
                ad.Fill(resultData);
                label5.Text = resultData.Rows.Count + " 条";
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出数据时出错：" + ex.Message, "数据导出工具", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            finally
            {
                oConn.Close();
            }
            if (txtGroupByColName.Text.Length > 0)
            {
                OutPutExcelByGroup(resultData);
            }
            else
            {
                OutPutExcel(resultData);
            }

            MessageBox.Show("已经成功导出", "数据导出工具", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}